package com.pinganfu.tesla.iwallet.util;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "pig.db";
	private static final int DATABASE_VERSION = 1;
	private static DBHelper helper;

	public static DBHelper initDBHelper(Context context) {
		if (helper == null) {
			helper = new DBHelper(context);
		}
		return helper;
	}

	private DBHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		// db.execSQL("CREATE TABLE accounts (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, account_type VARCHAR, name VARCHAR, bank_id VARCHAR, bank_name VARCHAR, card_number VARCHAR, encrypt_amount VARCHAR, credit_line FLOAT, bill_day INTEGER, current_period_flag INTEGER, pay_type VARCHAR, pay_day INTEGER, refund_type INTEGER, memo VARCHAR, active_flag INTEGER default 1, created_by VARCHAR, synced_at VARCHAR, external_id INTEGER,created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE pays (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, category VARCHAR, subcategory VARCHAR, category_name VARCHAR, encrypt_amount VARCHAR, surrency INTEGER, trade_date VARCHAR, member INTEGER, memo TEXT, allow_flag INTEGER, credit_card_flag INTEGER, unneed_flag INTEGER, input_from VARCHAR, account_id INTEGER, budget_flag INTEGER, created_by VARCHAR, synced_at VARCHAR, external_id INTEGER, accounting_item INTEGER, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL, state INTEGER default 0, real_unneed_amount VARCHAR default 0, image_name VARCHAR)");
		// db.execSQL("CREATE TABLE incomes (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, category VARCHAR, category_name VARCHAR, encrypt_amount VARCHAR, surrency INTEGER, trade_date VARCHAR, member INTEGER, memo TEXT, credit_card_flag INTEGER, account_id INTEGER, accounting_item INTEGER, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL, state INTEGER default 0 )");
		// db.execSQL("CREATE TABLE budgets (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, budget_date VARCHAR, category VARCHAR, encrypt_amount FLOAT, surrency INTEGER, plan_id VARCHAR, year VARCHAR, created_by VARCHAR, synced_at VARCHAR, external_id INTEGER,created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL, state INTEGER default 0)");
		// db.execSQL("CREATE TABLE templates (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, book_type VARCHAR, book_id INTEGER, category VARCHAR, subcategory VARCHAR, account_id INTEGER, member INTEGER, cached_count INTEGER default 0, memo VARCHAR, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL, name VARCHAR, to_account_id INTEGER, accounting_item VARCHAR, unneed INTEGER, allow_flag INTEGER, trade_type VARCHAR, category_name VARCHAR, active_flag INTEGER default 1)");
		// db.execSQL("CREATE TABLE members (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, name VARCHAR, active_flag INTEGER default 1, synced_at VARCHAR, created_by VARCHAR, updated_at VARCHAR NOT NULL, created_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE load_shares (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, borrower VARCHAR, des VARCHAR, repay_date VARCHAR, encrypt_amount VARCHAR, surrency VARCHAR, rate FLOAT, state INTEGER default 0, currency INTEGER, memo TEXT, remind INTEGER, trade_date VARCHAR, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE load_trades (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE ,id INTEGER, load_share_id INTEGER, trade_type VARCHAR, trade_date VARCHAR, encrypt_amount VARCHAR, change_type INTEGER, currency INTEGER, mome TEXT, account_id INTEGER, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, upadated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE borrow_shares (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, borrower VARCHAR, des VARCHAR, refund_date VARCHAR, encrypt_amount VARCHAR, surrency VARCHAR, rate FLOAT, state INTEGER default 0, currency INTEGER, memo TEXT, remind INTEGER, trade_date VARCHAR, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE common_trades (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE, id INTEGER, invest_id INTEGER, des VARCHAR, invest_type VARCHAR, trade_type VARCHAR, trade_date VARCHAR, encrypt_amount VARCHAR, change_type INTEGER, surrency VARCHAR, memo TEXT, created_by VARCHAR, synced_at VARCHAR, account_id VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE common_shares (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , id INTEGER, share_type VARCHAR, name VARCHAR, encrypt_amount VARCHAR, lookup_period INTEGER, lookup_period_unit varchar, due_date VARCHAR, expected_profit FLOAT, state INTEGER, currency INTEGER, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, upadated_at VARCHAR NOT NULL)");
		// db.execSQL("CREATE TABLE transfer (cid INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL  UNIQUE , id INTEGER, from_account_id INTEGER, to_account_id INTEGER, des VARCHAR, encrypt_amount VARCHAR, trade_date VARCHAR, surrency VARCHAR, memo VARCHAR, created_by VARCHAR, synced_at VARCHAR, created_at VARCHAR NOT NULL, updated_at VARCHAR NOT NULL, state INTEGER default 0)");
		// db.execSQL("CREATE VIEW accountview AS SELECT cid, date(trade_date) AS d, strftime('%H:%M', time(trade_date)) AS time, memo, encrypt_amount, category_name, 1 AS type, 0 AS unneed, datetime(trade_date) AS trade_time, account_id, '' AS invest_type, 0 AS change_type, '-1' AS invest_id, '' AS image_name FROM incomes UNION SELECT cid, date(trade_date) AS d, strftime('%H:%M', time(trade_date)) AS time, memo, encrypt_amount, category_name, 2 AS type, unneed_flag AS unneed, datetime(trade_date) AS trade_time, account_id, '' AS invest_type, 0 AS change_type, '-1' AS invest_id, image_name FROM pays UNION SELECT cid, date(trade_date) AS d, strftime('%H:%M', time(trade_date)) AS time, memo, encrypt_amount, des AS category_name, 3 AS type, 0 AS unneed, datetime(trade_date) AS trade_time, from_account_id AS account_id, '' AS invest_type, 0 AS change_type, '-1' AS invest_id, '' AS image_name FROM transfer UNION SELECT cid, date(trade_date) AS d, strftime('%H:%M', time(trade_date)) AS time, memo, encrypt_amount, des AS category_name, 4 AS type, 0 AS unneed, datetime(trade_date) AS trade_time, account_id, invest_type, change_type, invest_id, '' AS image_name FROM common_trades UNION SELECT cid, date(trade_date) AS d, strftime('%H:%M', time(trade_date)) AS time, memo, encrypt_amount, des AS category_name, 3 AS type, 0 AS unneed, datetime(trade_date) AS trade_time, to_account_id AS account_id, '' AS invest_type, 0 AS change_type, '-1' AS invest_id, '' AS image_name FROM transfer");
		// db.execSQL("CREATE VIEW borrowAndLoadview AS SELECT cid, id, borrower, des, refund_date AS repay_date, encrypt_amount, surrency, rate, state, currency, memo, remind, trade_date, created_by, synced_at, created_at, updated_at, 1 AS type FROM borrow_shares WHERE state = 0 UNION SELECT cid, id, borrower, des, repay_date, encrypt_amount, surrency, rate, state, currency, memo, remind, trade_date, created_by, synced_at, created_at, updated_at, 2 AS type FROM load_shares WHERE state = 0");
		// db.execSQL("CREATE TABLE messages (cid INTEGER PRIMARY KEY  AUTOINCREMENT NOT NULL UNIQUE,message VARCHAR, level INTEGER, unread INTEGER, type INTEGER, invest_type VARCHAR, invest_id INTEGER, created_at VARCHAR NOT NULL, delete_flag INTEGER  default 0, url VARCHAR)");

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

	}

}
